package com.company.ems.dao;

import com.company.ems.model.Employee;
import com.company.ems.util.DatabaseUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class EmployeeDAOImpl implements EmployeeDAO {
    
    @Override
    public List<Employee> findAll() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Employee> employees = new ArrayList<>();
        
        try {
            conn = DatabaseUtil.getConnection();
            String sql = "SELECT * FROM employees ORDER BY id DESC";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            
            while (rs.next()) {
                Employee employee = new Employee();
                employee.setId(rs.getInt("id"));
                employee.setName(rs.getString("name"));
                employee.setDepartment(rs.getString("department"));
                employee.setPosition(rs.getString("position"));
                employee.setSalary(rs.getBigDecimal("salary"));
                employee.setEmail(rs.getString("email"));
                employee.setPhone(rs.getString("phone"));
                employee.setHireDate(rs.getDate("hire_date"));
                employee.setCreatedBy(rs.getInt("created_by"));
                employee.setCreatedAt(rs.getTimestamp("created_at"));
                employee.setUpdatedAt(rs.getTimestamp("updated_at"));
                employees.add(employee);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.closeConnection(conn, pstmt, rs);
        }
        
        return employees;
    }
    
    @Override
    public Employee findById(int id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Employee employee = null;
        
        try {
            conn = DatabaseUtil.getConnection();
            String sql = "SELECT * FROM employees WHERE id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            
            if (rs.next()) {
                employee = new Employee();
                employee.setId(rs.getInt("id"));
                employee.setName(rs.getString("name"));
                employee.setDepartment(rs.getString("department"));
                employee.setPosition(rs.getString("position"));
                employee.setSalary(rs.getBigDecimal("salary"));
                employee.setEmail(rs.getString("email"));
                employee.setPhone(rs.getString("phone"));
                employee.setHireDate(rs.getDate("hire_date"));
                employee.setCreatedBy(rs.getInt("created_by"));
                employee.setCreatedAt(rs.getTimestamp("created_at"));
                employee.setUpdatedAt(rs.getTimestamp("updated_at"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.closeConnection(conn, pstmt, rs);
        }
        
        return employee;
    }
    
    @Override
    public boolean add(Employee employee) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean result = false;
        
        try {
            conn = DatabaseUtil.getConnection();
            String sql = "INSERT INTO employees (name, department, position, salary, email, phone, hire_date, created_by) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, employee.getName());
            pstmt.setString(2, employee.getDepartment());
            pstmt.setString(3, employee.getPosition());
            pstmt.setBigDecimal(4, employee.getSalary());
            pstmt.setString(5, employee.getEmail());
            pstmt.setString(6, employee.getPhone());
            pstmt.setDate(7, new java.sql.Date(employee.getHireDate().getTime()));
            pstmt.setInt(8, employee.getCreatedBy());
            
            int rows = pstmt.executeUpdate();
            result = rows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.closeConnection(conn, pstmt);
        }
        
        return result;
    }
    
    @Override
    public boolean update(Employee employee) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean result = false;
        
        try {
            conn = DatabaseUtil.getConnection();
            String sql = "UPDATE employees SET name = ?, department = ?, position = ?, salary = ?, email = ?, phone = ?, hire_date = ? WHERE id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, employee.getName());
            pstmt.setString(2, employee.getDepartment());
            pstmt.setString(3, employee.getPosition());
            pstmt.setBigDecimal(4, employee.getSalary());
            pstmt.setString(5, employee.getEmail());
            pstmt.setString(6, employee.getPhone());
            pstmt.setDate(7, new java.sql.Date(employee.getHireDate().getTime()));
            pstmt.setInt(8, employee.getId());
            
            int rows = pstmt.executeUpdate();
            result = rows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.closeConnection(conn, pstmt);
        }
        
        return result;
    }
    
    @Override
    public boolean delete(int id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean result = false;
        
        try {
            conn = DatabaseUtil.getConnection();
            String sql = "DELETE FROM employees WHERE id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            
            int rows = pstmt.executeUpdate();
            result = rows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.closeConnection(conn, pstmt);
        }
        
        return result;
    }
    
    @Override
    public List<Employee> findByCondition(String name, String department, String position) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Employee> employees = new ArrayList<>();
        
        try {
            conn = DatabaseUtil.getConnection();
            StringBuilder sql = new StringBuilder("SELECT * FROM employees WHERE 1=1");
            
            if (name != null && !name.trim().isEmpty()) {
                sql.append(" AND name LIKE ?");
            }
            if (department != null && !department.trim().isEmpty()) {
                sql.append(" AND department = ?");
            }
            if (position != null && !position.trim().isEmpty()) {
                sql.append(" AND position = ?");
            }
            sql.append(" ORDER BY id DESC");
            
            pstmt = conn.prepareStatement(sql.toString());
            
            int paramIndex = 1;
            if (name != null && !name.trim().isEmpty()) {
                pstmt.setString(paramIndex++, "%" + name + "%");
            }
            if (department != null && !department.trim().isEmpty()) {
                pstmt.setString(paramIndex++, department);
            }
            if (position != null && !position.trim().isEmpty()) {
                pstmt.setString(paramIndex++, position);
            }
            
            rs = pstmt.executeQuery();
            
            while (rs.next()) {
                Employee employee = new Employee();
                employee.setId(rs.getInt("id"));
                employee.setName(rs.getString("name"));
                employee.setDepartment(rs.getString("department"));
                employee.setPosition(rs.getString("position"));
                employee.setSalary(rs.getBigDecimal("salary"));
                employee.setEmail(rs.getString("email"));
                employee.setPhone(rs.getString("phone"));
                employee.setHireDate(rs.getDate("hire_date"));
                employee.setCreatedBy(rs.getInt("created_by"));
                employee.setCreatedAt(rs.getTimestamp("created_at"));
                employee.setUpdatedAt(rs.getTimestamp("updated_at"));
                employees.add(employee);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.closeConnection(conn, pstmt, rs);
        }
        
        return employees;
    }
}